package pt.fct.mpc.lastfm.database;

import android.app.SearchManager;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class LastfmDatabaseAdapter
{
	public static final String LASTFM_DATABASE_TYPE_ARTIST = "Artist";
	public static final String LASTFM_DATABASE_TYPE_ALBUM = "Album";
	public static final String LASTFM_DATABASE_TYPE_TRACK = "Track";
	public static final String LASTFM_DATABASE_TYPE_EVENT = "Event";
	
	protected static final String LASTFM_TABLE_USER_EVENTS = "user_events";
	protected static final String LASTFM_TABLE_USER_FRIENDS = "user_friends";
	protected static final String LASTFM_TABLE_USER_TOP_ARTISTS = "user_top_artists";
	protected static final String LASTFM_TABLE_USER_TOP_ALBUMS = "user_top_albums";
	protected static final String LASTFM_TABLE_USER_TOP_TRACKS = "user_top_tracks";
	protected static final String LASTFM_TABLE_VENUES = "venues";
	protected static final String LASTFM_TABLE_ALBUM_ARTISTS = "album_artists";
	protected static final String LASTFM_TABLE_ARTISTS = "artists";
	protected static final String LASTFM_TABLE_ALBUMS = "albums";
	protected static final String LASTFM_TABLE_EVENT_ARTISTS = "event_artists";
	protected static final String LASTFM_TABLE_TRACKS = "tracks";
	
	public static final String LASTFM_TABLE_SEARCH_HISTORY = "search_history";
	public static final String LASTFM_TABLE_FAVOURITES = "favourites";
	
	protected static final String LASTFM_DB_CREATE_USER_EVENTS =
		"create table "+LASTFM_TABLE_USER_EVENTS+" ("
		+"id integer primary key,"
		+"title text non null,"
		+"event_artists integer,"
		+"venue_id integer,"
		+"start_date date, "
		+"description text non null,"
		+"image text non null,"
		+"attendance integer,"
		+"url text non null,"
		+"website text non null"
		+");";
	
	protected static final String LASTFM_DB_CREATE_USER_FRIENDS =
		"create table "+LASTFM_TABLE_USER_FRIENDS+" ("
		+"friend_name text non null,"
		+"image text non null,"
		+"url text non null"
		+") primary key (user_name, friend_name);";
	
	protected static final String LASTFM_DB_CREATE_USER_TOP_ARTISTS =
		"create table "+LASTFM_TABLE_USER_TOP_ARTISTS+" ("
		+"artist_id text references "+LASTFM_TABLE_ARTISTS+" (mbid),"
		+"rank integer"
		+") primary key (user_id, artist_id);";
	
	protected static final String LASTFM_DB_CREATE_USER_TOP_ALBUMS =
		"create table "+LASTFM_TABLE_USER_TOP_ALBUMS+" ("
		+"id integer primary key autoincrement,"
		+"album_id integer references "+LASTFM_TABLE_ALBUMS+" (mbid),"
		+"playcount integer,"
		+"rank integer"
		+");";

	protected static final String LASTFM_DB_CREATE_USER_TOP_TRACKS =
		"create table "+LASTFM_TABLE_USER_TOP_TRACKS+" ("
		+"id integer primary key autoincrement, "
		+"track_id integer references "+LASTFM_TABLE_TRACKS+" (track_id),"
		+"playcount integer,"
		+"rank integer"
		+");";
	
	protected static final String LASTFM_DB_CREATE_VENUES =
		"create table "+LASTFM_TABLE_VENUES+" ("
		+"id integer primary key,"
		+"name text non null,"
		+"city text non null,"
		+"country text non null,"
		+"street text non null,"
		+"postal_code text non null,"
		+"latitude text non null,"
		+"longitude text non null,"
		+"url text non null"
		+");";
	
	protected static final String LASTFM_DB_CREATE_ALBUM_ARTISTS =
		"create table "+LASTFM_TABLE_ALBUM_ARTISTS+" ("
		+"album_id text references "+LASTFM_TABLE_ALBUMS+" (mbid),"
		+"artist_id text references "+LASTFM_TABLE_ARTISTS+" (mbid)"
		+") unique (album_id, artist_id);";
	
	protected static final String LASTFM_DB_CREATE_ALBUMS =
		"create table "+LASTFM_TABLE_ALBUMS+" ("
		+"mbid text primary key non null,"
		+"name text non null,"
		+"url text non null,"
		+"image text non null"
		+");";
	
	protected static final String LASTFM_DB_CREATE_ARTISTS =
		"create table "+LASTFM_TABLE_ARTISTS+" ("
		+"mbid text primary key non null,"
		+"name text non null,"
		+"url text non null"
		+");";
	
	protected static final String LASTFM_DB_CREATE_EVENT_ARTISTS =
		"create table "+LASTFM_TABLE_EVENT_ARTISTS+" ("
		+"event_id integer references "+LASTFM_TABLE_USER_EVENTS+" (id),"
		+"artist_id text references "+LASTFM_TABLE_ARTISTS+" (mbid)"
		+") primary key (event_id, artist_id);";
	
	protected static final String LASTFM_DB_CREATE_TRACKS =
		"create table "+LASTFM_TABLE_TRACKS+" ("
		+"track_id integer primary key autoincrement,"
		+"mbid text non null,"
		+"name text non null,"
		+"artist_id text references "+LASTFM_TABLE_ARTISTS+" (mbid)"
		+") unique (mbid, name, artist_id);";
	
	/*
	 * Keep a search history.
	 */
	protected static final String LASTFM_TABLE_SEARCH_HISTORY_COL_TEXT =
		SearchManager.SUGGEST_COLUMN_TEXT_1;
	protected static final String LASTFM_TABLE_SEARCH_HISTORY_COL_TYPE =
		SearchManager.SUGGEST_COLUMN_TEXT_2;
	protected static final String LASTFM_TABLE_SEARCH_HISTORY_COL_INTENT = 
		SearchManager.SUGGEST_COLUMN_QUERY;
	protected static final String LASTFM_TABLE_SEARCH_HISTORY_COL_INTENT_EXTRA =
		SearchManager.SUGGEST_COLUMN_INTENT_EXTRA_DATA;
	
	protected static final String LASTFM_DB_CREATE_SEARCH_HISTORY =
		"create table "+LASTFM_TABLE_SEARCH_HISTORY+" ("
		+"_id integer primary key autoincrement,"
		+LASTFM_TABLE_SEARCH_HISTORY_COL_TEXT+" text non null,"
		+LASTFM_TABLE_SEARCH_HISTORY_COL_TYPE+" text non null,"
		+LASTFM_TABLE_SEARCH_HISTORY_COL_INTENT+" text non null,"
		+LASTFM_TABLE_SEARCH_HISTORY_COL_INTENT_EXTRA+" text non null,"
		+"hits integer"
		+");";
	
	/*
	 * Keep user-defined favourites.
	 */
	protected static final String LASTFM_TABLE_FAVOURITES_NAME = "name";
	protected static final String LASTFM_TABLE_FAVOURITES_TYPE = "type";
	
	protected static final String LASTFM_DB_CREATE_FAVOURITES =
		"create table "+LASTFM_TABLE_FAVOURITES+" ("
		+"_id integer primary key autoincrement,"
		+LASTFM_TABLE_FAVOURITES_NAME+" text non null,"
		+LASTFM_TABLE_FAVOURITES_TYPE+" text non null"
		+");";
	
	protected static final String LASTFM_DB_NAME = "lastfm";
	protected static final int LASTFM_DB_VERSION = 5;
	
	
	private static LastfmDatabaseAdapter instance = null;
	private static DatabaseHelper db_helper;
	protected static SQLiteDatabase db_conn;

	protected Context context;
	
	private static class DatabaseHelper
		extends SQLiteOpenHelper
	{

		public DatabaseHelper(Context context)
		{
			super(context, LASTFM_DB_NAME, null, LASTFM_DB_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db)
		{
			db.execSQL(LASTFM_DB_CREATE_SEARCH_HISTORY);
			db.execSQL(LASTFM_DB_CREATE_FAVOURITES);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
		{
			db.execSQL("DROP TABLE IF EXISTS "+LASTFM_TABLE_SEARCH_HISTORY);
			db.execSQL("DROP TABLE IF EXISTS "+LASTFM_TABLE_FAVOURITES);
			onCreate(db);
		}
	}
	
	protected LastfmDatabaseAdapter(Context context)
	{
		this.context = context;
	}
	
	static public LastfmDatabaseAdapter getInstance(Context context)
	{
		LastfmDatabaseAdapter db_adapter = new LastfmDatabaseAdapter(context);
		if (instance == null)
			instance = db_adapter;
		
		return db_adapter;
	}
	
	
	public SQLiteDatabase open()
	{
		if (db_helper == null)
			db_helper = new DatabaseHelper(this.context);
		
		if (db_conn == null)
			db_conn = db_helper.getWritableDatabase();
		
		return db_conn;
	}
	
	public void close()
	{
		db_conn.close();
		db_helper.close();
	}
	
	public SQLiteDatabase getConn()
	{
		return this.open();
	}
}
